package com.nccsjz.back.cardway.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oracle.sql.CLOB;
import utils.DBUtils;

import com.nccsjz.pojo.CardWay;
import com.nccsjz.utils.DateUtil;
import com.nccsjz.base.BaseDAO;
import com.nccsjz.utils.Pager;

/**
 * 用卡知识的CRUD操作
 * */
public class CardWayDAO extends BaseDAO {

	private boolean flag;

	public CardWayDAO(Connection con) {
		super(con);
	}

	// 添加用卡知识
	public boolean addCardWay(CardWay cardWay) throws SQLException {
		try {
			String sql = "insert into cardWay(Wtitle,Wcontent,Wdate) values(?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, cardWay.getWtitle());
			//pstmt.setString(2, cardWay.getWcontent());
			CLOB clob = DBUtils.getCLOBFromString(con, cardWay.getWcontent());
			pstmt.setClob(2, clob);
			if (null != cardWay.getWdate()) {
				pstmt.setDate(3, DateUtil.convertDate(cardWay.getWdate()));
			} else {
				pstmt.setDate(3, DateUtil.convertDate(new java.util.Date()));
			}
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// 修改用卡知识
	public boolean updateCardWay(CardWay cardWay) throws SQLException {
		try {
			String sql = "update cardWay set Wtitle=?,Wcontent=? where Wid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, cardWay.getWtitle());
			//pstmt.setString(2, cardWay.getWcontent());
			CLOB clob = DBUtils.getCLOBFromString(con, cardWay.getWcontent());
			pstmt.setClob(2, clob);
			// pstmt.setDate(3, DateUtil.convertDate(cardWay.getWdate()));
			pstmt.setLong(3, cardWay.getWid());
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// 根据主键获取一条用卡知识
	public CardWay getCardWay(Long id) throws SQLException {
		CardWay cardWay = null;
		try {

			String sql = "select * from cardWay where Wid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				cardWay = new CardWay();
				cardWay.setWid(rs.getLong("Wid"));
				cardWay.setWtitle(rs.getString("Wtitle"));
				//cardWay.setWcontent(rs.getString("Wcontent"));
				CLOB clob = (CLOB) rs.getClob("Wcontent");
				cardWay.setWcontent((DBUtils.getStringFromCLOB(clob)));
				cardWay.setWdate(rs.getDate("Wdate"));
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return cardWay;
	}

	// 根据主键删除用卡知识
	public boolean deleteCardWay(long id) throws SQLException {
		try {
			// conn=super.getConn();
			String sql = "delete from cardWay where Wid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// 分页处理
	public Pager getCardWay(int pageSize, int pageNo) throws SQLException {
		List<CardWay> list = new ArrayList<CardWay>();
		int rownum = 0;
		try {
			String sql1 = "select count(*) from cardWay";
			//String sql2 = "select * from(select u.*,rownum rn from(select * from cardWay order by Wdate desc) u where rownum<=?)where rn>?";
			
			String sql2 = "select * from (select cardWay.*,row_number() over(order by wid desc) rn from cardWay) u " 
					+ " where u.rn > ? and u.rn <= ? ";
			
			pstmt = con.prepareStatement(sql1);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rownum = rs.getInt(1);
			}
			pstmt = con.prepareStatement(sql2);
			pstmt.setInt(1, (pageNo - 1) * pageSize);
			pstmt.setInt(2, pageNo * pageSize);			
			rs = pstmt.executeQuery();
			while (rs.next()) {
				CardWay cardWay = new CardWay();
				cardWay.setWid(rs.getLong("Wid"));
				cardWay.setWtitle(rs.getString("Wtitle"));
				//cardWay.setWcontent(rs.getString("Wcontent"));
				CLOB clob = (CLOB) rs.getClob("Wcontent");
				cardWay.setWcontent((DBUtils.getStringFromCLOB(clob)));
				cardWay.setWdate(rs.getDate("Wdate"));
				list.add(cardWay);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		Pager pager = new Pager(pageSize, pageNo, rownum, list);
		return pager;
	}

	// 批量删除
	public boolean deleteBathCardWay(List<Long> list) throws SQLException {
		int num[] = null;
		try {
			String sql = "delete from cardWay where Wid=?";
			pstmt = con.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				pstmt.setLong(1, list.get(i));
				pstmt.addBatch();
			}
			num = pstmt.executeBatch();
			if (num != null) {
				flag = true;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);

		}
		return flag;
	}

	/**
	 * 得到前n条用卡知识。
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<CardWay> getTopN(int n) throws SQLException {
		List<CardWay> list = new ArrayList<CardWay>();
		int rownum = 0;
		try {
			if (n <= 0) {
				throw new Exception("取得的记录条数不能为0或负数");
			}
			String sql2 = "select * from(select u.*,rownum rn from(select * from cardWay order by Wid desc) u )where rn<=?";

			pstmt = con.prepareStatement(sql2);
			pstmt.setInt(1, n);

			rs = pstmt.executeQuery();
			while (rs.next() && rownum <= n) {
				CardWay cardWay = new CardWay();
				cardWay.setWid(rs.getLong("Wid"));
				cardWay.setWtitle(rs.getString("Wtitle"));
				//cardWay.setWcontent(rs.getString("Wcontent"));
				CLOB clob = (CLOB) rs.getClob("Wcontent");
				cardWay.setWcontent((DBUtils.getStringFromCLOB(clob)));
				cardWay.setWdate(rs.getDate("Wdate"));
				list.add(cardWay);
				rownum++;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return list;
	}
}
